class MysqlSystem {
  constructor(sequelize, dataTypeFormat) {
    this.sequelize = sequelize
    this.dataTypeFormat = dataTypeFormat

    // 测试数据库连接
    this.sequelize.authenticate().then(() => {
      console.log('Connection has been established successfully.')
    }).catch(err => {
      throw new Error('Unable to connect to the database:' + err)
    })
  }

 /**
   * 获取数据库下的表信息
   * @return {Object[]} 数据表信息
   * - {String} TABLE_NAME 数据表名
   * - {String} TABLE_COMMENT 表的注释、备注
   */
  async getDataTables() {
    let sql = ` select TABLE_NAME, TABLE_COMMENT `
            + ` from information_schema.TABLES ` 
            + ` where TABLE_SCHEMA=(select database()) `

    let dbTables = await this.sequelize.query(sql, {raw: true, type: 'SELECT'})
    return dbTables
  }

  /**
   * 获取数据表下的列信息
   * @return {Object[]} 数据列信息
   * - {String} COLUMN_NAME 列名称
   * - {String} DATA_TYPE 数据类型
   * - {Number} CHARACTER_MAXIMUM_LENGTH  字符最大长度
   * - {String} COLUMN_KEY  关键列 true/false
   * - {String} COLUMN_COMMENT  字段注释、描述
   */
  async getTableColumns(tableName) {
    let sql = ` select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, if(COLUMN_KEY='PRI', true, false) COLUMN_KEY, COLUMN_COMMENT  `
            + ` from information_schema.COLUMNS `
            + ` where TABLE_SCHEMA = (select database()) `
            + ` and TABLE_NAME="${tableName}" `
            
    let tableColumns = await this.sequelize.query(sql, {raw: true, type: 'SELECT'})
    tableColumns.forEach(column => column.DATA_TYPE = this.dataTypeFormat.format(column.DATA_TYPE))
    return tableColumns
  }
}

module.exports = MysqlSystem
